package com.meixun.xuanke.course.dao;

import com.meixun.xuanke.course.model.Course;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.HashMap;
import java.util.List;

public interface CourseDao {
    /**
     * 查询学校选课配置
     * @param schoolId
     * @return
     */
    @Select("select * from x_config where schoolid=#{arg0}")
    HashMap<String,Object> getConfig(@Param("arg0") String schoolId);
    /**
     * 查询学校选课配置
     * @param termTypeid
     * @return
     */
    @Select("select * from x_term_type where schoolid=#{arg0}")
    HashMap<String,Object> getXTermType(@Param("arg0") Integer termTypeid);



    /**
     * 查询学校分类
     * @param schoolId
     * @return
     */
    @Select("select id,name from x_subtype WHERE schoolid=#{arg0}")
    List< HashMap<String,Object>> querySubType(@Param("arg0") String schoolId);



    /**
     * 查询最新可用的学期
     * @return
     */
    @Select("select TermID from term WHERE `status`=1")
    List< HashMap<String,Object>> queryTermOne();
    /**
     * 获取所有可以选课的学校
     * @return
     */
    @Select("SELECT schoolid FROM x_config WHERE state=1;")
    List<HashMap<String,Object>> getCourseSchoolList();


    /**
     * 查询学校24小时可以选课的课程列表
     * @param schoolId
     * @return
     */
    @Select("SELECT cou.id id,cou.subId,sub.type typeid,cou.type type,sub.grade,CONCAT(sub.`name`,cou.`name`) coursename,cou.termTypeid,cou.stime,cou.etime,cou.timeText,cou.timeJson,cou.size maxsize,IFNULL(courseSize.size,0) size,cou.type FROM x_course cou " +
            "LEFT JOIN x_subject sub ON sub.id=cou.subId LEFT JOIN (SELECT sc.courseid id,count(sc.id) size FROM x_student_course sc " +
            "WHERE sc.schoolid=#{arg0} AND sc.termid=#{arg1} AND sc.state !=0 GROUP BY (sc.courseid)) courseSize ON cou.id=courseSize.id " +
            "WHERE (UNIX_TIMESTAMP(cou.stime)-UNIX_TIMESTAMP(NOW()))< (60*60*24) AND cou.etime> NOW() AND cou.schoolid=#{arg0} AND cou.termid=#{arg1} ORDER BY cou.stime;")
    List<Course> queryCourseListBySchool(@Param("arg0") String schoolId,@Param("arg1") Integer termid);

    /**
     * 查询学生某学期的选课ids
     * @return
     */
    @Select("SELECT GROUP_CONCAT(courseid) ids FROM x_student_course WHERE studentid=#{arg0} AND schoolid= #{arg1} AND termid=#{arg2} and state!=0;")
    String getMyCourseIds(@Param("arg0")String studentid,@Param("arg1")String schoolid,@Param("arg2")Integer termid);

    /**
     * 查询课程详情
     * @param id
     * @return
     */
    @Select("SELECT cou.id id,cou.subId,CONCAT(sub.`name`,cou.`name`) coursename,sub.price,sub.grade,cou.type,cou.stime,cou.etime,cou.timeText,cou.timeJson,cou.size maxsize,cou.localName,cou.text courseText,sub.text subText," +
            "(SELECT GROUP_CONCAT(t.TeacherName,'') FROM teacher t WHERE FIND_IN_SET(t.UserID,cou.teacher)) teacherName FROM x_course cou LEFT JOIN x_subject sub ON sub.id=cou.subId WHERE cou.id=#{arg0}")
    Course getCourseById(@Param("arg0") Integer id);


    /**
     * 查询学生某学期的选课
     * @return
     */
    @Select("SELECT c.id,CONCAT(s.`name`,c.`name`) coursename,c.timeText,c.timeJson,c.subId FROM x_student_course sc LEFT JOIN x_course c ON c.id=sc.courseid LEFT JOIN x_subject s ON s.id=c.subId WHERE" +
            " sc.studentid =#{arg0} AND sc.schoolid=#{arg1} AND sc.termid =#{arg2} and state!=0;")
    List<Course> getMyCourseList(@Param("arg0")String studentid,@Param("arg1")String schoolid,@Param("arg2")Integer termid);

    /**
     * 查询某个课程的数量
     * @param courseId
     * @return
     */
    @Select("select COUNT(sc.id) as num from x_student_course sc where sc.courseid=#{arg0} and sc.state<>0;")
    Long getCourseSize(@Param("arg0")Integer courseId);


}
